libname raw "...\Views";
libname lmdb "...\LMDB";
libname ext "...\Eksterne data";
libname psyk "...\Grunddata";


libname workdata "...\Generic data";


******************************************************************************************************************;
* (1): Export relevant datasets
* The dataset contains information about clinics and the doctors working in the clinics
******************************************************************************************************************;


proc export data=ext.T_yder_1 outfile="...\work\T_yder_1" dbms=stata replace;
run;

proc export data=ext.T_yder_person outfile="...\work\T_yder_person" dbms=stata replace;
run;

proc export data=ext.Registration outfile="...\work\Registration" dbms=stata replace;
run;


******************************************************************************************************************;
* (1): Find relevant population: 
******************************************************************************************************************;

proc sql;
create table work.children1 as select DISTINCT
pnr, mor_id, far_id, KOEN, alder, kom, fainupdSourceYear as year
from raw.Fainupdv 
;
quit;


proc sql;
create table work.mom as select DISTINCT
mor_id as pnr
from work.children1 
;
quit;

proc sql;
create table work.dad as select DISTINCT
far_id as pnr
from work.children1 
;
quit;


*Education from uddaupd;
proc sql;
create table work.children2 as select DISTINCT
a.pnr,
b.HFAUDD, input(b.HFPRIA, best.) as HFPRIA, b.UDD, b.UDD_N, b.IGINSTNR, b.uddaupd02SourceYear as year
from work.children1 as a 
left join raw.uddaupd02v as b on a.pnr=b.PNR
;
quit;


proc sql;
create table work.children3 as select DISTINCT
b.pnr, b.HFAUDD, HFPRIA, b.UDD, b.UDD_N, b.IGINSTNR, b.year
from work.children2 as b
where HFAUDD ne .
;
quit;



proc sql;
create table work.mom_udd as select DISTINCT
a.pnr,
b.HFAUDD
from work.mom as a 
left join raw.uddaupd02v as b on a.pnr=b.PNR
where HFAUDD ne .
;
quit;


proc sql;
create table work.dad_udd as select DISTINCT
a.pnr,
b.HFAUDD
from work.dad as a 
left join raw.uddaupd02v as b on a.pnr=b.PNR
where HFAUDD ne .
;
quit;


proc export data=children1 outfile="...\work\children1" dbms=stata replace;
run;

proc export data=children3 outfile="...\work\children3" dbms=stata replace;
run;


proc export data=mom_udd outfile="...\work\mom_udd" dbms=stata replace;
run;

proc export data=dad_udd outfile="...\work\dad_udd" dbms=stata replace;
run;

**********************************************************************************
* Deaths
**********************************************************************************;

proc sql;
create table work.patient_dod as select DISTINCT
pnr, C_DOD_1A, C_DOD_1b, C_DOD_1c, C_DOD_1d, D_DODSDATO, D_FINDEDATO, D_STATDATO, C_DODTILGRUNDL_ACME
from raw.Dodsaasgv
;
quit;

proc sql;
create table work.patient_dod2 as select DISTINCT
pnr, C_DOD1, C_DOD2, C_DOD3, C_DOD4, D_DODSDTO
from raw.Dodsaarsv
;
quit;


proc export data=work.patient_dod outfile="...\work\patient_dod_moreyears" dbms=stata replace;
run;
proc export data=work.patient_dod2 outfile="...\work\patient_dod2_moreyears" dbms=stata replace;
run;




**********************************************************************************
* Find patients x GP matches - first steps done here
**********************************************************************************;


proc sql;
create table work.gp_all1 as select
ydernr, afrper, pnr, barnmak, honuge, sikgrup, input(ydtyp, best.) as ydtyp, PATGRP as pattyp, speciale, ydltid, sssyupd02SourceYear as year
from raw.Sssyupd02v 
;
quit;


proc sql;
create table work.gp_all2 as select
ydernr, afrper, pnr, barnmak, honuge, sikgrup, input(ydtyp, best.) as ydtyp , pattyp, henvisni, speciale, sysiupdSourceYear as year
from raw.Sysiupdv 
;
quit;


data work.gp_all_new_test;
set work.gp_all1 work.gp_all2;
where barnmak ne '1' and substr(SPECIALE,1,2) = "80" and sikgrup ="1" and (pattyp = "1" or pattyp = "01");
run;


proc sql;
create table work.gp_all_new_test2 as select DISTINCT 
ydernr, afrper, pnr, honuge, ydtyp, henvisni, year, ydltid
from work.gp_all_new_test
;
quit;



proc export data=gp_all_new_test2 outfile="...\work\gp_all_new3" dbms=stata replace;
run;



**********************************************************************************
* GP Visits and services
**********************************************************************************;


* GP visits I;
proc sql;
create table work.gp_all1 as select
ydernr, pnr, honuge, sikgrup, speciale, PATGRP as pattyp, sssyupd02SourceYear as year
from raw.Sssyupd02v 
where barnmak ne "1" and substr(SPECIALE,1,2) = "80" and sikgrup ="1" and (pattyp = "1" or pattyp = "01");
;
quit;


* GP visits II;
proc sql;
create table work.gp_all2 as select
ydernr, pnr, honuge, sikgrup, speciale, pattyp, sysiupdSourceYear as year
from raw.Sysiupdv 
where barnmak ne "1" and substr(SPECIALE,1,2) = "80" and sikgrup ="1" and (pattyp = "1" or pattyp = "01");
;
quit;

* Append and sort data;
data work.gp_all_new;
set work.gp_all1 work.gp_all2;
run;

proc sort data = gp_all_new;
by pnr year ydernr;
run;


proc sql;
create table summary2 as select
pnr, ydernr, year, count(speciale) as services
from work.gp_all_new 
group by pnr, year
;
quit;


proc sql;
create table work.services as select DISTINCT
pnr, year, services
from summary2 
;
quit;


proc sql;
create table work.gp_all_new2 as select DISTINCT
ydernr, pnr, honuge, year
from gp_all_new 
;
quit;


proc sql;
create table summary as select
pnr, year, count(year) as N_visits
from work.gp_all_new2 
group by pnr, year
;
quit;



*Diabetes checkups;
proc sql;
create table work.Diabetes_Checkup as select DISTINCT
pnr, year
from work.gp_all_new
where (SPECIALE= "804549" or SPECIALE= "800108" or SPECIALE= "800107" or SPECIALE= "800106" )
;
quit;


data Diabetes_Checkup2;
diabetes_control=1;
set Diabetes_Checkup;
run;



data GP_contact;
	merge 	work.summary
			work.services;
	by pnr year;
run;


data GP_contact2;
	merge 	work.GP_contact
			work.Diabetes_Checkup2;
	by pnr year;
run;



proc export data=GP_contact2 outfile="...\work\GP_contact" dbms=stata replace;
run;


**********************************************************************************
* Costs per patient
**********************************************************************************;



proc sql;
create table work.gp_all_costs_gp as select 
ydernr, pnr, bruhon, speciale, PATGRP as pattyp, input(ydtyp, best.) as ydtyp, sssyupd02SourceYear as year
from raw.Sssyupd02v 
where barnmak ne '1';
;
quit;



proc sql;
create table work.gp_all_costs_gp2 as select
ydernr, pnr, bruhon, speciale, pattyp, input(ydtyp, best.) as ydtyp, sysiupdSourceYear as year
from raw.Sysiupdv 
where barnmak ne '1';
;
quit;


proc export data=gp_all_costs_gp outfile="...\work\gp_all_costs_gp" dbms=stata replace;
run;

proc export data=gp_all_costs_gp2 outfile="...\work\gp_all_costs_gp2" dbms=stata replace;
run;



**********************************************************************************
* Patient background info
**********************************************************************************;


proc sql;
create table work.patients as select DISTINCT
pnr
from work.gp_all_new;
quit;

proc sql;
create table work.patient_background as select DISTINCT
a.pnr,
b.ALDER, b.CIVST, b.E_FAELLE_ID, b.IE_TYPE, b.KOEN, input(b.KOM, best.) as KOM, b.befupd01SourceYear as year
from work.patients as a
left join raw.Befupd01v as b on a.pnr=b.PNR 
where befupd01SourceYear ne . and a.pnr=b.PNR
;
quit;

proc export data=patient_background outfile="...\work\patient_background_moreyears" dbms=stata replace;
run;



**********************************************************************************
* Hospitalizations
**********************************************************************************;

proc sql;
create table work.patient_hospital as select DISTINCT
a.pnr,
b.C_PATTYPE, b.C_SGH, b.C_SPEC, b.D_INDDTO, b.D_UDDTO, b.RECNUM, b.V_SENGDAGE, b.lpradmSourceYear as year
from work.patients as a
left join raw.lpradmv as b on a.pnr=b.PNR
where a.pnr=b.PNR
;
quit;


proc sql;
create table work.recnum as select DISTINCT
RECNUM
from work.patient_hospital
;
quit;

proc sql;
create table work.diagnosis as select DISTINCT
a.RECNUM,
b.C_DIAG, b.C_DIAGMOD, b.C_DIAGTYPE, b.C_TILDIAG, b.lprdiagupdSourceYear as year
from work.recnum as a
left join raw.lprdiagupdv as b on a.RECNUM=b.RECNUM
;
quit;

proc export data=diagnosis outfile="...\work\diagnosis_moreyears" dbms=stata replace;
run;

proc export data=patient_hospital outfile="...\work\patient_hospital_moreyears" dbms=stata replace;
run;



**********************************************************************************
* Prescriptions
**********************************************************************************;


%macro do_list;
%do I=1995 %to 2021;

proc sql;
create table work.lmdb_&I. as select distinct
a.pnr, 
b.recu_ydernr, b.apk, b.volume, b.voltypecode, b.packsize, b.eksd, b.ATC
from work.patients as a 
join lmdb.lmdb&I. as b on a.pnr=b.PNR
where a.pnr=b.PNR and (substr(ATC,1,3) = "B01" | substr(ATC,1,3) = "C07" | substr(ATC,1,3) = "C09" | substr(ATC,1,3) = "A10" | substr(ATC,1,5) = "C10AA" |substr(ATC,1,7) = "R03AC18" | substr(ATC,1,7) = "R03BB04" | substr(ATC,1,7) = "R03BB05" | substr(ATC,1,7) = "R03BB06" | substr(ATC,1,7) = "R03BB07" | substr(ATC,1,7) = "R03DX07" | substr(ATC,1,5) = "R03AC" | substr(ATC,1,5) = "R03CC" | substr(ATC,1,5) = "R03BB")
;
quit;

%end;
%mend do_list;

%do_list


data workdata.lmdb;
set work.lmdb_1995;
run;

%macro do_list3;
%do I=1996 %to 2021;

data workdata.lmdb;
set workdata.lmdb work.lmdb_&I.;
run;

%end;
%mend do_list3;

%do_list3


proc export data=workdata.lmdb outfile="...\work\lmdb_moreyears" dbms=stata replace;
run;




**********************************************************************************
* Lung scans
**********************************************************************************;


proc sql;
create table work.lung_cancer_test as select DISTINCT
C_OPR, D_ODTO, recnum
from raw.Lprsksubupdv where substr(C_OPR,1,4) = "KGDA" | substr(C_OPR,1,6) = "UXCC75" | substr(C_OPR,1,6) = "UXCC77" | substr(C_OPR,1,6) = "UXRC00" | substr(C_OPR,1,6) = "UXRC05" | substr(C_OPR,1,6) = "UXRC10" | substr(C_OPR,1,6) = "UXRC12"
;
quit;


proc sql;
create table work.lung_cancer_test2 as select DISTINCT
a.C_OPR, a.D_ODTO, a.recnum,
b.pnr
from work.lung_cancer_test as a
left join raw.lpradmv as b on a.RECNUM=b.RECNUM where substr(C_OPR,1,4) = "KGDA" | substr(C_OPR,1,6) = "UXCC75" | substr(C_OPR,1,6) = "UXCC77" | substr(C_OPR,1,6) = "UXRC00" | substr(C_OPR,1,6) = "UXRC05" | substr(C_OPR,1,6) = "UXRC10" | substr(C_OPR,1,6) = "UXRC12"
;
quit;


proc export data=work.lung_cancer_test2 outfile="...\work\lung_cancer_test2_moreyears" dbms=stata replace;
run;





**********************************************************************;
* Income;
**********************************************************************;

proc sql;
create table work.income as select
pnr, arledgr, lonind, pstill, idapupdSourceYear as year
from raw.Idapupdv
;
quit;

proc export data=income outfile="...\work\income" dbms=stata replace;
run;




**********************************************************************;
* High school grades;
**********************************************************************;

proc export data=Raw.Udgv outfile="...\work\All_GRADES" dbms=stata replace; run;



